﻿Imports System.Data.SqlClient
Imports System.Configuration

Public Class MantenimientoBasico

    Private mConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")

    Sub Agregar(ByVal mantenimiento As Ejemplo.BE.MantenimientoBasico)
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = mConnectionString


            sqlCommand.Parameters.Add(New SqlParameter("Nombre", mantenimiento.Nombre))
            sqlCommand.Parameters.Add(New SqlParameter("Descripcion", mantenimiento.Descripcion))

            sqlConnection.Open()

            sqlCommand.CommandText = "MantenimientoBasicoAgregar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Insertar en la Bd" & Ex.ToString)
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

    End Sub

    Function Editar(ByVal mantenimiento As Ejemplo.BE.MantenimientoBasico) As Boolean
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = mConnectionString


            sqlCommand.Parameters.Add(New SqlParameter("Nombre", mantenimiento.Nombre))
            sqlCommand.Parameters.Add(New SqlParameter("Descripcion", mantenimiento.Descripcion))
            sqlCommand.Parameters.Add(New SqlParameter("Id", mantenimiento.Id))

            sqlConnection.Open()

            sqlCommand.CommandText = "MantenimientoBasicoEditar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Editar en la Bd" & Ex.ToString)
            Return False
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

        Return True
    End Function


    Function Eliminar(ByVal id As Integer) As Boolean
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = mConnectionString
            sqlCommand.Parameters.Add(New SqlParameter("Id", id))

            sqlConnection.Open()

            sqlCommand.CommandText = "MantenimientoBasicoEliminar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Editar en la Bd" & Ex.ToString)
            Return False
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

        Return True
    End Function

    Function Listar() As List(Of Ejemplo.BE.MantenimientoBasico)

        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        Dim myReader As SqlDataReader
        Dim lista As New List(Of Ejemplo.BE.MantenimientoBasico)

        Try
            sqlConnection.ConnectionString = mConnectionString

            sqlConnection.Open()

            sqlCommand.CommandText = "MantenimientoBasicoListar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection

            myReader = sqlCommand.ExecuteReader()

            If myReader.HasRows Then
                Do While myReader.Read()

                    Dim mantenimiento As New Ejemplo.BE.MantenimientoBasico
                    mantenimiento.Id = CInt(myReader.GetValue(0))
                    mantenimiento.Nombre = myReader.GetValue(1).ToString
                    mantenimiento.Descripcion = myReader.GetValue(2).ToString
                    lista.Add(mantenimiento)

                Loop
        
            End If

            myReader.Close()
           
        Catch Ex As Exception
            Throw New Exception("Problemas al listar" & Ex.ToString)
        Finally

            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try


        Return lista
    End Function


    Function Obtener(ByVal id As Integer) As Ejemplo.BE.MantenimientoBasico
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        Dim myReader As SqlDataReader
        Dim resultado As Ejemplo.BE.MantenimientoBasico = Nothing

        Try
            sqlConnection.ConnectionString = mConnectionString

            sqlConnection.Open()

            sqlCommand.CommandText = "MantenimientoBasicoObtener"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection

            sqlCommand.Parameters.Add(New SqlParameter("Id", id))

            myReader = sqlCommand.ExecuteReader()

            If myReader.HasRows Then
                Do While myReader.Read()
                    resultado = New Ejemplo.BE.MantenimientoBasico
                    resultado.Id = CInt(myReader.GetValue(0))
                    resultado.Nombre = myReader.GetValue(1).ToString
                    resultado.Descripcion = myReader.GetValue(2).ToString
                Loop

            End If

            myReader.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Obtener" & Ex.ToString)
        Finally

            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try


        Return resultado
    End Function


    Function Filtrar(ByVal mantenimiento As Ejemplo.BE.MantenimientoBasico) As List(Of Ejemplo.BE.MantenimientoBasico)

        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        Dim myReader As SqlDataReader
        Dim lista As New List(Of Ejemplo.BE.MantenimientoBasico)

        Try
            sqlConnection.ConnectionString = mConnectionString

            sqlConnection.Open()

            sqlCommand.CommandText = "MantenimientoBasicoFiltrar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection


            sqlCommand.Parameters.Add(New SqlParameter("Nombre", mantenimiento.Nombre))
            sqlCommand.Parameters.Add(New SqlParameter("Descripcion", mantenimiento.Descripcion))



            myReader = sqlCommand.ExecuteReader()

            If myReader.HasRows Then
                Do While myReader.Read()

                    Dim mantenimientoBasicoBE As New Ejemplo.BE.MantenimientoBasico
                    mantenimientoBasicoBE.Id = CInt(myReader.GetValue(0))
                    mantenimientoBasicoBE.Nombre = myReader.GetValue(1).ToString
                    mantenimientoBasicoBE.Descripcion = myReader.GetValue(2).ToString
                    lista.Add(mantenimientoBasicoBE)

                Loop

            End If

            myReader.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al listar" & Ex.ToString)
        Finally

            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try


        Return lista

    End Function


End Class
